package rpt.yto.flex.remote.data.dao.processor;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import rpt.yto.flex.remote.data.bean.CorpBean;
import rpt.yto.flex.remote.data.bean.DeptdocBean;
import rpt.yto.flex.remote.data.biz.QueryBiz;
import rpt.yto.flex.remote.data.dao.itf.IPubObject;
import rpt.yto.util.ConvertFunc;

@SuppressWarnings({"unchecked"})
public class Query_allpsn {

	
	private Map<String , String> distinctionMap = null;
	private StringBuilder builderJoinTransit;
	private Map<String , List<Map<String , Object>>> deptMap = null;
	private String period = null;
	private String directDept = " ";
	
	private static Query_allpsn querySqlFunc = new Query_allpsn();
	
	private Query_allpsn() {
		
	}
	
	public static final Query_allpsn getInstance() {
		return querySqlFunc;
	}
	
	/**
	 *   全网人员变更表 - 全网人员类别
	 *   
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeAll(String period) throws Exception {
		this.period = period;
		
		StringBuilder builderTypeAll = new StringBuilder();
		
		builderTypeAll.append(" select 1 rowno , '全网合计' statistictype , sum(s_person) s_person , sum(induty) induty , sum(dimission) dimission , ");
		builderTypeAll.append(" sum(transin) transin , sum(transout) transout , sum(e_person) e_person  from ( ");
		builderTypeAll.append(" select pk_corp , sum(s_person) s_person , sum(induty) induty , sum(dimission) dimission ,  ");
		builderTypeAll.append(" sum(transin) transin , sum(transout) transout , sum(e_person) e_person  from ( ");

		// 期初
		builderTypeAll.append(" select bd_psndoc.pk_corp ,  count(1) s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeAll.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeAll.append(" and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and bd_psndoc.indutydate < '"+period+"' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp ");
		builderTypeAll.append(" union all  ");
		// 本期入
		builderTypeAll.append(" select bd_psndoc.pk_corp , 0 s_person , count(1) induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeAll.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeAll.append("  and bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp ");
		builderTypeAll.append(" union all  ");
		// 本期离
		builderTypeAll.append(" select hi_psndoc_dimission.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person  "); 
		builderTypeAll.append(" from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeAll.append(" where hi_psndoc_dimission.pk_psndoc = bd_psndoc.pk_psndoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeAll.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" group by hi_psndoc_dimission.pk_corp ");
		builderTypeAll.append(" union all  ");
		builderTypeAll.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeAll.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeAll.append(" where  ");
		builderTypeAll.append(" bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and bd_psndoc.psnclscope <> 0 ");
		builderTypeAll.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
		builderTypeAll.append(" and bd_psndoc.outdutydate is not null and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and bd_psndoc.pk_psndoc not in  ");
		builderTypeAll.append(" ( select hi_psndoc_dimission.pk_psndoc  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeAll.append(" where bd_psndoc.pk_psndoc(+) = hi_psndoc_dimission.pk_psndoc ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeAll.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"%' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~')  ) ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp ");
		builderTypeAll.append(" union all  ");
		// 本期调入
		builderTypeAll.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , count(1) transin , 0 transout , 0 e_person ");
		builderTypeAll.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate like '"+period+"%') and bd_psnbasdoc.joinsysdate <> bd_psndoc.indutydate and bd_psndoc.indutydate like '"+period+"' || '%' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp ");
		builderTypeAll.append(" union all  ");
		// 本期调出
		builderTypeAll.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person  ");
		builderTypeAll.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builderTypeAll.append(" and bd_psndoc.psnclscope = 4  ");
		builderTypeAll.append(" and bd_psndoc.outdutydate like '"+period+"' || '%'  ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~')  ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp  ");
		
		builderTypeAll.append(" union all  ");
		// 期末
		builderTypeAll.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , count(1) e_person ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" group by bd_psndoc.pk_corp ) group by pk_corp ) ");
		
		return builderTypeAll;
		
	}
	
	/**
	 * 全网人员变更表 - 直营机构人员类别
	 * 
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeDirect(String period , Integer rowno , String statistictype , String isworkingunit) throws Exception {
		
		StringBuilder builderTypeDirect = new StringBuilder();
		
		builderTypeDirect.append(" select "+rowno+" rowno , '"+statistictype+"' statistictype , sum(s_person) s_person , sum(induty) induty , sum(dimission) dimission , ");
		builderTypeDirect.append(" sum(transin) transin , sum(transout) transout , sum(e_person) e_person  from ( ");
		// 期初
		builderTypeDirect.append(" select bd_psndoc.pk_corp ,  count(1) s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeDirect.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and indutydate < '"+period+"' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		
		builderTypeDirect.append(" group by bd_psndoc.pk_corp ");

		builderTypeDirect.append(" union all  ");

		// 本期入
		builderTypeDirect.append(" select bd_psndoc.pk_corp , 0 s_person , count(1) induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeDirect.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeDirect.append("  and bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeDirect.append(" group by bd_psndoc.pk_corp ");

		builderTypeDirect.append(" union all  ");

		// 本期离
		builderTypeDirect.append(" select hi_psndoc_dimission.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person  ");
		builderTypeDirect.append(" from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeDirect.append(" where hi_psndoc_dimission.pk_psndoc = bd_psndoc.pk_psndoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeDirect.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" group by hi_psndoc_dimission.pk_corp ");

		builderTypeDirect.append(" union all  ");

		builderTypeDirect.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person ");
		builderTypeDirect.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and bd_psndoc.psnclscope <> 0 ");
		builderTypeDirect.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
		builderTypeDirect.append(" and bd_psndoc.outdutydate is not null ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and bd_psndoc.pk_psndoc not in  ");
		builderTypeDirect.append(" ( select hi_psndoc_dimission.pk_psndoc  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeDirect.append(" where bd_psndoc.pk_psndoc(+) = hi_psndoc_dimission.pk_psndoc ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeDirect.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" ) group by bd_psndoc.pk_corp ");



		builderTypeDirect.append(" union all  ");

		// 本期调入
		builderTypeDirect.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , count(1) transin , 0 transout , 0 e_person ");
		builderTypeDirect.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate like '"+period+"%') and bd_psnbasdoc.joinsysdate <> indutydate and indutydate like '"+period+"' || '%' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeDirect.append(" group by bd_psndoc.pk_corp ");

		builderTypeDirect.append(" union all  ");

		// 本期调出
		builderTypeDirect.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person  ");
		builderTypeDirect.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builderTypeDirect.append(" and bd_psndoc.psnclscope = 4  ");
		builderTypeDirect.append(" and bd_psndoc.outdutydate like '"+period+"' || '%'  ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.isworkingunit = '"+isworkingunit+"'  ");
		builderTypeDirect.append(" group by bd_psndoc.pk_corp  ");

		builderTypeDirect.append(" union all  ");

		// 期末
		builderTypeDirect.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , count(1) e_person ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeDirect.append(" group by bd_psndoc.pk_corp) ");
		
		return builderTypeDirect;
	}
	
	/**
	 * 
	 * 全网人员变更表 - distinctionMap中的类别
	 * 
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeByMap(String period) throws Exception {
		
		if(distinctionMap == null)
			initDistinctionMap();
		
		StringBuilder builderTypeAppend2 = new StringBuilder();
		
		Iterator iter = distinctionMap.entrySet().iterator();
		int rowno = 3;
		while(iter.hasNext()) {
			
			Entry entry = (Entry) iter.next();
			String distinction = (String) entry.getKey();
			String statistictype = (String) entry.getValue();
			
			StringBuilder builderTypeByMap = new StringBuilder();
			
			builderTypeByMap.append(" select "+rowno+" rowno , '"+statistictype+"' statistictype , sum(s_person) s_person , sum(induty) induty , sum(dimission) dimission , ");
			builderTypeByMap.append(" sum(transin) transin , sum(transout) transout , sum(e_person) e_person  from ( ");
			// 期初
			builderTypeByMap.append(" select bd_psndoc.pk_corp ,  count(1) s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and indutydate < '"+period+"' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by bd_psndoc.pk_corp ");

			builderTypeByMap.append(" union all  ");

			// 本期入
			builderTypeByMap.append(" select bd_psndoc.pk_corp , 0 s_person , count(1) induty , 0 dimission , 0 transin , 0 transout , 0 e_person ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by bd_psndoc.pk_corp ");

			builderTypeByMap.append(" union all  ");

			// 本期离
			builderTypeByMap.append(" select hi_psndoc_dimission.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
			builderTypeByMap.append(" where hi_psndoc_dimission.pk_psndoc = bd_psndoc.pk_psndoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
			builderTypeByMap.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by hi_psndoc_dimission.pk_corp ");

			builderTypeByMap.append(" union all  ");

			builderTypeByMap.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and bd_psndoc.psnclscope <> 0 ");
			builderTypeByMap.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
			builderTypeByMap.append(" and bd_psndoc.outdutydate is not null ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and bd_psndoc.pk_psndoc not in  ");
			builderTypeByMap.append(" (select hi_psndoc_dimission.pk_psndoc  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
			builderTypeByMap.append(" where bd_psndoc.pk_psndoc(+) = hi_psndoc_dimission.pk_psndoc ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
			builderTypeByMap.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" )group by bd_psndoc.pk_corp ");



			builderTypeByMap.append(" union all  ");

			// 本期调入
			builderTypeByMap.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , count(1) transin , 0 transout , 0 e_person ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate like '"+period+"%') and bd_psnbasdoc.joinsysdate <> indutydate and indutydate like '"+period+"' || '%' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by bd_psndoc.pk_corp ");

			builderTypeByMap.append(" union all  ");

			// 本期调出
			builderTypeByMap.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person  ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
			builderTypeByMap.append(" and bd_psndoc.psnclscope = 4  ");
			builderTypeByMap.append(" and bd_psndoc.outdutydate like '"+period+"' || '%'  ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by bd_psndoc.pk_corp  ");
			

			builderTypeByMap.append(" union all  ");

			// 期末
			builderTypeByMap.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , count(1) e_person ");
			builderTypeByMap.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(getDirectDept());
			builderTypeByMap.append(" group by bd_psndoc.pk_corp) ");
			
			System.out.println(builderTypeByMap.toString());
			
			builderTypeAppend2.append(builderTypeByMap).append(" union all ");
			
			rowno ++;
			
		}
		
		builderTypeAppend2 = new StringBuilder(builderTypeAppend2.substring(0 , builderTypeAppend2.length() - " union all ".length()));
		
		return builderTypeAppend2;
	}
	
	public final StringBuilder getQueryTypeOper(String period) throws Exception {
		
		StringBuilder builderTypeOper = new StringBuilder();
		
		builderTypeOper.append(" select 9 rowno , '其中：加盟中转（中转操作人员）' statistictype , sum(s_person) s_person , sum(induty) induty , sum(dimission) dimission , ");
		builderTypeOper.append(" sum(transin) transin , sum(transout) transout , sum(e_person) e_person  from ( ");
		// 期初
		builderTypeOper.append(" select bd_psndoc.pk_corp ,  count(1) s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person  ");
		builderTypeOper.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and indutydate < '"+period+"' and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeOper.append(" group by bd_psndoc.pk_corp ");
		builderTypeOper.append(" union all  ");
		// 本期入
		builderTypeOper.append(" select bd_psndoc.pk_corp , 0 s_person , count(1) induty , 0 dimission , 0 transin , 0 transout , 0 e_person  ");
		builderTypeOper.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+) ");
		builderTypeOper.append("  and bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeOper.append(" group by bd_psndoc.pk_corp ");
		builderTypeOper.append(" union all  ");
		// 本期离
		builderTypeOper.append(" select hi_psndoc_dimission.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeOper.append(" where hi_psndoc_dimission.pk_psndoc = bd_psndoc.pk_psndoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeOper.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" group by hi_psndoc_dimission.pk_corp ");
		builderTypeOper.append(" union all  ");
		builderTypeOper.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person  ");
		builderTypeOper.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and bd_psndoc.psnclscope <> 0 ");
		builderTypeOper.append("  and bd_psnbasdoc.joinsysdate like '"+period+"' || '%' ");
		builderTypeOper.append(" and bd_psndoc.outdutydate is not null and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and bd_psndoc.pk_psndoc not in  ");
		builderTypeOper.append(" (select hi_psndoc_dimission.pk_psndoc  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp ");
		builderTypeOper.append(" where bd_psndoc.pk_psndoc(+) = hi_psndoc_dimission.pk_psndoc ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp ");
		builderTypeOper.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '"+period+"' || '%' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" ) group by bd_psndoc.pk_corp ");
		builderTypeOper.append(" union all  ");
		// 本期调入
		builderTypeOper.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , count(1) transin , 0 transout , 0 e_person  ");
		builderTypeOper.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate like '"+period+"%') and bd_psnbasdoc.joinsysdate <> indutydate and indutydate like '"+period+"' || '%' and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeOper.append(" group by bd_psndoc.pk_corp ");
		builderTypeOper.append(" union all  ");
		// 本期调出
		builderTypeOper.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person  ");
		builderTypeOper.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builderTypeOper.append(" and bd_psndoc.psnclscope = 4  ");
		builderTypeOper.append(" and bd_psndoc.outdutydate like '"+period+"' || '%'  ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeOper.append(" group by bd_psndoc.pk_corp  ");		
		

		builderTypeOper.append(" union all  ");

		// 期末
		builderTypeOper.append(" select bd_psndoc.pk_corp , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , count(1) e_person  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and indutydate <= '"+period+"~' and ");
		builderTypeOper.append(getJoinTransit());
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		builderTypeOper.append(" group by bd_psndoc.pk_corp  ) ");
		
		return builderTypeOper;
	}
	
	protected final Map<String , String> initDistinctionMap() throws Exception {
		
		if(distinctionMap != null)
			return distinctionMap;
		
		
		distinctionMap = new TreeMap<String, String>(             
			new Comparator<Object>() {
				public int compare(Object obj1, Object obj2) {                     
					String v1 = (String)obj1;
					String v2 = (String)obj2;
					int s = v1.compareTo(v2);
					return s;
				}             
			}          
		);
		
		distinctionMap.put("'ORGCORP00000000A0015'", "一、总公司");
		distinctionMap.put("'ORGCORP00000000A0016'", "二、管理区");
		distinctionMap.put("'ORGCORP00000000A0017'", "三、直营省区域公司");
		distinctionMap.put("'ORGCORP00000000A0018'", "四、直营中心");
		distinctionMap.put("'ORGCORP00000000A0019'", "五、直营公司");
		
		// 直营分公司 过滤分部
		List<Map<String , Object>> corpList = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(" select pk_corp from bd_corp where (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction = 'ORGCORP00000000A0019' and bd_corp.isworkingunit = 'Y' order by bd_corp.unitcode " , CorpBean.class);
		initDirectDept(corpList);
		
		
		
		return distinctionMap;
		
	}
	
	private final void initDirectDept(List<Map<String , Object>> corpList) throws Exception {
		
		StringBuilder builderResult = new StringBuilder();
		
		for(Map<String , Object> corpMap : corpList) {
			
			List<Map<String , Object>> deptList = (List<Map<String, Object>>) QueryBiz.getInstance().executeQuery("select deptcode from bd_deptdoc where length(deptcode) = 6 and pk_corp = '"+corpMap.get("pk_corp")+"'" , DeptdocBean.class);
			
			if(deptList == null || deptList.size() == 0)
				continue;
			
			StringBuilder builderDeptcode = new StringBuilder();
			for(Map<String , Object> dept : deptList) {
				
				if(dept.get("deptcode") == null || "".equals(dept.get("deptcode")))
					continue;
				
				String queryString = "select to_char(wmsys.wm_concat(chr(39) || pk_deptdoc || chr(39))) || ',' pk_deptdoc from bd_deptdoc where deptcode like '"+dept.get("deptcode")+"%' and pk_corp = '"+corpMap.get("pk_corp")+"'";
				List<Map<String , Object>> deptMap = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(queryString , DeptdocBean.class);
				
				if(",".equals(deptMap.get(0).get("pk_deptdoc")))
					continue ;
				
				builderDeptcode.append(deptMap.get(0).get("pk_deptdoc"));
				
			}
			
			if(builderDeptcode.length() > 0)
				builderResult.append(" and bd_psndoc.pk_deptdoc not in ("+builderDeptcode.substring(0 , builderDeptcode.length() - 1)+") ");
			
		}
		
		if(builderResult.length() > 0)
			setDirectDept(builderResult.toString());
		
	}
	
	private final StringBuilder getJoinTransit() throws Exception {
		
		if(builderJoinTransit != null)
			return builderJoinTransit;
		
		List<Map<String , Object>> corpMap = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(" select to_char(wmsys.wm_concat(chr(39) || pk_corp || chr(39))) pk_corp from bd_corp where unitcode in ("+ConvertFunc.getInstance().change(IPubObject.JOINCORP , "'" , "'" , ",")+") " , CorpBean.class);
		initJoinCorp(String.valueOf(corpMap.get(0).get("pk_corp")));
		
		List<Map<String , Object>> resultList = deptMap.get(corpMap.get(0).get("pk_corp"));
		Map<Integer , String> indexMap = new ConcurrentHashMap<Integer , String>();
		StringBuilder builderPk_deptdoc = new StringBuilder();
		
		int total = 0;
		int row = 0;
		int index = 0;
		
		for(Map<String , Object> result : resultList) {
			builderPk_deptdoc.append(result.get("pk_deptdoc")).append(",");
			
			row ++;
			total ++;
			if(row % 500 == 0) {
				row = 0;
				indexMap.put(index, builderPk_deptdoc.substring(0 , builderPk_deptdoc.length() - 1));
				index ++;
				builderPk_deptdoc = new StringBuilder();
				
				continue;
			}
			
			if(resultList.size() == total) {
				
				indexMap.put(index, builderPk_deptdoc.substring(0 , builderPk_deptdoc.length() - 1));
				
			}
			
			
		}
		
		builderJoinTransit = new StringBuilder();
		builderJoinTransit.append(" bd_corp.unitcode in ("+ConvertFunc.getInstance().change(IPubObject.JOINCORP , "'" , "'" , ",")+") ");
		
		Iterator iter = indexMap.entrySet().iterator();
		while(iter.hasNext()) {
			Entry entry = (Entry) iter.next();
			builderJoinTransit.append(" and bd_psndoc.pk_deptdoc not in ("+entry.getValue()+") ");
		}
		
		builderJoinTransit.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') ");
		return builderJoinTransit;
	}
	
	private final void initJoinCorp(String pk_corp) throws Exception {
		
		if(deptMap == null)
			deptMap = new ConcurrentHashMap<String , List<Map<String , Object>>>();
		
		if(deptMap.get(pk_corp) == null) {
			List<Map<String , Object>> deptList = (List<Map<String, Object>>) QueryBiz.getInstance().executeQuery("select deptcode from bd_deptdoc where length(deptcode) = 6 and pk_corp in ("+pk_corp+")" , DeptdocBean.class);
	
			List<Map<String , Object>> pk_deptdocList = new ArrayList<Map<String , Object>>();
			for(Map<String , Object> dept : deptList) {
				
				String queryString = "select chr(39) || pk_deptdoc || chr(39) pk_deptdoc from bd_deptdoc where deptcode like '"+dept.get("deptcode")+"%' and pk_corp in ("+pk_corp+")";
				pk_deptdocList.addAll((List<Map<String, Object>>) QueryBiz.getInstance().executeQuery(queryString , DeptdocBean.class));
				
			}
			
			deptMap.put(pk_corp, pk_deptdocList);
		}
	}

	protected final String getDirectDept() {
		return directDept;
	}

	private final void setDirectDept(String directDept) {
		this.directDept = directDept;
	}

	public final String getQuerySQL(String period) throws Exception {
		
		String respSQL = getQueryTypeAll(period)
				.append(" union all ").append(getQueryTypeDirect(period , 2 , "其中：直营机构人员" , "Y"))
				.append(" union all ").append(getQueryTypeByMap(period))
				.append(" union all ").append(getQueryTypeDirect(period , 8 , "六、加盟公司" , "N"))
				.append(" union all ").append(getQueryTypeOper(period)).toString();
		
//		System.out.println(respSQL);
		
		return respSQL;
		
	}
	
}
